/*
 * File Name  :BaseService.java
 * Create Date:2012-11-3 下午5:20:51
 * Author     :woden
 */

package com.wodensoft.core.db;

import java.io.Serializable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

/**
 * 基础的dao类.
 * 
 */
public abstract class BaseService {
    /**
     * HQL和SQL语句处理器.参考 {@link POHelper$Condition}
     */
    private static class QueryHQLBuilder {

        /**
         * 构建查询语句. <br>
         * 
         * @param queryMap 查询MAP
         * @return
         */
        @SuppressWarnings("rawtypes")
        public static String build(Map queryMap) {
            return buildWhere(queryMap) + buildOrder(queryMap);
        }

        /**
         * 生成排序字段 排序格式： _orderby_fieldname:asc/desc
         * 
         * @param queryMap 查询MAP
         * @return
         */
        @SuppressWarnings("rawtypes")
        private static String buildOrder(Map queryMap) {
            if (null == queryMap || queryMap.isEmpty())
                return "";

            Set entrySet = queryMap.entrySet();
            Iterator it = entrySet.iterator();
            StringBuffer ordbyBuff = new StringBuffer(" order by ");
            while (it.hasNext()) {
                Map.Entry entry = (Map.Entry) it.next();
                String key = (String) entry.getKey();
                String value = null;
                if (entry.getValue().getClass() == String.class) {
                    value = (String) entry.getValue();
                }

                if (!key.startsWith("_orderby_")) {// 不是_orderby打头的就直接路过
                    continue;
                } else if (value == null || value.trim().equals("")) {// 不包含升序或降序字符,则跳过
                    continue;
                } else {
                    ordbyBuff.append(getFieldName(key));
                    if ("desc".equalsIgnoreCase(value)) {
                        ordbyBuff.append(" desc");
                    } else {
                        ordbyBuff.append(" asc");
                    }
                    ordbyBuff.append(",");
                }
            }

            if (ordbyBuff.toString().endsWith(",")) {// 去掉最后的逗号
                return ordbyBuff.substring(0, ordbyBuff.lastIndexOf(","));
            } else {
                return ordbyBuff.toString().equals(" order by ") ? "" : ordbyBuff.toString();
            }

        }

        /**
         * 构建查询语句. <br>
         * 
         * <pre>
         * eg:
         * where name = value order by name asc.
         * </pre>
         * 
         * @param queryMap 查询MAP
         * @return
         */
        @SuppressWarnings("rawtypes")
        public static String buildWhere(Map queryMap) {

            // 无查询条件调用
            if (null == queryMap || queryMap.isEmpty())
                return "";

            Set entrySet = queryMap.entrySet();
            Iterator it = entrySet.iterator();
            StringBuffer whereBuff = new StringBuffer();
            while (it.hasNext()) {
                Map.Entry entry = (Map.Entry) it.next();
                String key = (String) entry.getKey();

                String value = null;
                if (entry.getValue().getClass() == String.class) {
                    value = (String) entry.getValue();
                } else if (entry.getValue().getClass() == String[].class) {
                    value = "not null";
                }

                if (null == value || value.trim().equals("")) {// 无参数传入则不做为查询条件
                    continue;
                } else if (getCommand(key).equals("sql")) {
                    whereBuff.append(getConnetType(key)).append("(" + value + ")");
                } else if (getCommand(key).equals("sisn")) {
                    if ("0".equals(value)) {
                        whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is null ");
                    } else if ("1".equals(value)) {
                        whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is not null ");
                    }
                } else if (getCommand(key).equals("se")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" = ").append("'")
                            .append(value).append("'");
                } else if (getCommand(key).equals("sl")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" like ").append("'%")
                            .append(value).append("%'");
                } else if (getCommand(key).equals("sne")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" != ").append("'")
                            .append(value).append("'");
                } else if (getCommand(key).equals("snl")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" not like ").append("'%")
                            .append(value).append("%'");
                } else if (getCommand(key).equals("snn")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is not null ");
                } else if (getCommand(key).equals("sn")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is null ");
                } else if (getCommand(key).equals("sin")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" in (");
                    String[] vals = (String[]) entry.getValue();
                    for (int i = 0; i < vals.length; i++) {
                        if (i != 0) {
                            whereBuff.append(",");
                        }
                        whereBuff.append("'").append(vals[i]).append("'");

                        if (i == vals.length - 1) {
                            whereBuff.append(")");
                        }
                    }
                } else if (getCommand(key).equals("snin")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" not in (");
                    String[] vals = (String[]) entry.getValue();
                    for (int i = 0; i < vals.length; i++) {
                        if (i != 0) {
                            whereBuff.append(",");
                        }
                        whereBuff.append("'").append(vals[i]).append("'");

                        if (i == vals.length - 1) {
                            whereBuff.append(")");
                        }
                    }
                } else if (getCommand(key).equals("nisn")) {
                    if ("0".equals(value)) {
                        whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is null ");
                    } else if ("1".equals(value)) {
                        whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is not null ");
                    }
                } else if (getCommand(key).equals("ne")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" = ").append(value);
                } else if (getCommand(key).equals("nb")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" > ").append(value);
                } else if (getCommand(key).equals("nbe")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" >= ").append(value);
                } else if (getCommand(key).equals("ns")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" < ").append(value);
                } else if (getCommand(key).equals("nse")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" <= ").append(value);
                } else if (getCommand(key).equals("nnn")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is not null ");
                } else if (getCommand(key).equals("nne")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" != ").append(value);
                } else if (getCommand(key).equals("nn")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" is null ");
                } else if (getCommand(key).equals("nin")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" in (");
                    String[] vals = (String[]) entry.getValue();
                    for (int i = 0; i < vals.length; i++) {
                        if (i != 0) {
                            whereBuff.append(",");
                        }
                        whereBuff.append(vals[i]);

                        if (i == vals.length - 1) {
                            whereBuff.append(")");
                        }
                    }
                } else if (getCommand(key).equals("nnin")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" not in (");
                    String[] vals = (String[]) entry.getValue();
                    for (int i = 0; i < vals.length; i++) {
                        if (i != 0) {
                            whereBuff.append(",");
                        }
                        whereBuff.append(vals[i]);

                        if (i == vals.length - 1) {
                            whereBuff.append(")");
                        }
                    }
                } else if (getCommand(key).equals("dnm")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" <= '").append(value)
                            .append("'");
                } else if (getCommand(key).equals("dnl")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" >= '").append(value)
                            .append("'");
                } else if (getCommand(key).equals("de")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" between '").append(value)
                            .append("' and dateadd(day,1,'").append(value).append("')");

                } else if (getCommand(key).equals("dne")) {
                    whereBuff.append(getConnetType(key)).append(getFieldName(key)).append(" != '").append(value)
                            .append("'");
                }

            }

            String hql = null;
            if (whereBuff.toString().startsWith(" and ")) {// 去掉最开始的and连接符
                hql = whereBuff.substring(whereBuff.indexOf(" and") + 4, whereBuff.length());
            }

            if (hql != null) {
                return "where" + hql;
            }

            return "";
        }

        /**
         * 获取查询连接符
         * 
         * @param key
         * @return
         */
        private static String getConnetType(String key) {
            return " and ";
        }

        /**
         * 获取查询命令.
         * 
         * @param key
         * @return
         */
        private static String getCommand(String key) {
            return key.substring(key.indexOf("_") + 1, key.indexOf("_", 1));
        }

        /**
         * 获取查询字段PO对应属性名
         * 
         * @param key
         * @return
         */
        private static String getFieldName(String key) {
            return key.substring(key.indexOf("_", 1) + 1, key.length());// 第二个_开始
        }
    }

    /**
     * <code>SessionFactory</code>对象.
     */
    private SessionFactory sessionFactory;

    /**
     * 执行自定义HQL操作.
     * 
     * @param hql
     * @param values
     * @throws DbException
     */
    public void executeHQL(String hql, Object... values) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            Query query = session.createQuery(hql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
            }
            query.executeUpdate();
        } catch (Throwable e) {
            throw new DbException("执行[" + hql + "]出错.", e);
        }
    }

    /**
     * 根据查询条件查找唯一记录.
     * 
     * @param entryName
     * @param queryMap
     * @return
     * @throws DbException
     */
    @SuppressWarnings("rawtypes")
    public Map find(String entryName, Map queryMap) throws DbException {
        String hql = "from " + entryName + " " + QueryHQLBuilder.build(queryMap);
        Session session = this.sessionFactory.getCurrentSession();
        try {
            Query query = session.createQuery(hql);
            return (Map) query.uniqueResult();
        } catch (Throwable e) {
            throw new DbException("查询[" + hql + "]出错.", e);
        }
    }

    /**
     * 自定义查找.
     * 
     * @param entryName 对象名.
     * @param keyName 字段Key.
     * @param keyValue 字段值.
     * @return 满足条件的唯一对象.
     */
    @SuppressWarnings({ "rawtypes" })
    public Map findByKey(String entryName, String keyName, Object keyValue) {
        Session session = this.sessionFactory.getCurrentSession();
        Query query = session.createQuery("from " + entryName + " where " + keyName + " = ?");
        query.setParameter(0, keyValue);
        List result = query.list();
        if (result != null && result.size() > 0) {
            return (Map) result.get(0);
        } else {
            return null;
        }
    }

    /**
     * 查找.
     * 
     * @param entryName 对象名.
     * @param pk 主键.
     * @return 返回实例.
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Map findByPk(String entryName, Serializable pk) {
        Session session = this.sessionFactory.getCurrentSession();
        Map<String, Object> entry = (Map<String, Object>) session.get(entryName, pk);
        if (entry == null) {
            return null;
        } else {
            return entry;
        }
    }

    /**
     * 自定义HQL查询.
     * 
     * @param hql HQL语句.
     * @param values 入参数组.
     * @return 结果对象.
     * @throws DbException
     *             数据操作失败时抛出.
     */
    @SuppressWarnings("rawtypes")
    public Map findHQL(String hql, Object... values) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            Query query = session.createQuery(hql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
            }
            return (Map) query.uniqueResult();
        } catch (Throwable e) {
            throw new DbException("查询[" + hql + "]出错.", e);
        }
    }

    /**
     * 自定义SQL查询.
     * 
     * @param sql sql语句.
     * @param values 替换入参.
     * @return 结果对象.
     * @throws DbException
     */
    public Map<?, ?> findSQL(String sql, Object... values) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            SQLQuery query = session.createSQLQuery(sql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
            }
            return (Map<?, ?>) query.uniqueResult();
        } catch (Throwable e) {
            throw new DbException("查询[" + sql + "]出错.", e);
        }
    }

    /**
     * 获取表记录总数.
     * 
     * @param entryName 对象名.
     * @return 当前表记录总数.
     * @throws DbException
     */
    public Long getCount(String entryName) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        Long count = null;
        try {
            count = (Long) session.createQuery("select count(*) from " + entryName).list().iterator().next();
            return count;
        } catch (Throwable e) {
            throw new DbException("查询[" + entryName + "]总数出错.", e);
        }

    }

    /**
     * 获取表记录总数.
     * 
     * @param entryName 对象名.
     * @param queryMap 动态查询条件.
     * @return 符合条件的记录总数.
     * @throws DbException
     */
    @SuppressWarnings("rawtypes")
    public Long getCount(String entryName, Map queryMap) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        Long count = null;
        try {
            String hql = "select count(*) from " + entryName + " " + QueryHQLBuilder.buildWhere(queryMap);
            count = (Long) session.createQuery(hql).list().iterator().next();
            return count;
        } catch (Throwable e) {
            throw new DbException("查询[" + entryName + "]总数出错.", e);
        }

    }

    /**
     * 从PO对象中获取对象名.
     * 
     * @param po 动态对象.
     * @return 对象名.
     */
    @SuppressWarnings({ "rawtypes" })
    protected String getEntryName(Map po) throws DbException {
        if (po != null && po.containsKey("$type$")) {
            return (String) po.get("$type$");
        }

        throw new DbException("对象无指定实体.");
    }

    /**
     * 自定义HQL查询.
     * 
     * @param queryMap 动态查询入参,参考:{@link BaseService.QueryHQLBuilder}.
     * @return 结果数组.
     * @throws DbException 查询出错时抛出.
     */
    @SuppressWarnings("rawtypes")
    public List<Map> query(String entryName, Map queryMap) throws DbException {
        String hql = "from " + entryName + " " + QueryHQLBuilder.build(queryMap);
        return this.queryHQL(hql);
    }

    /**
     * 查找所有
     * 
     * @param entryName 对象名.
     * @return 单表所有记录.
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Map> queryAll(String entryName) {
        Session session = this.sessionFactory.getCurrentSession();
        return session.createQuery("from " + entryName).list();
    }

    /**
     * 自定义HQL查询.
     * 
     * @param hql HQL语句.
     * @param values 入参数组.
     * @return 结果列表.
     * @throws DbException 数据操作失败时抛出.
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Map> queryHQL(String hql, Object... values) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            Query query = session.createQuery(hql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
            }
            return query.list();
        } catch (Throwable e) {
            throw new DbException("查询[" + hql + "]出错.", e);
        }
    }

    /**
     * 自定义HQL分页查询.
     * 
     * @param hql HQL语句.
     * @param firstResult 记录开端.
     * @param maxResult 最大记录数.
     * @param values 入参数组.
     * @return 结果列表.
     * @throws DbException 数据操作失败时抛出.
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public List<Map> queryHQLPage(String hql, int firstResult, int maxResult, Object... values) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            Query query = session.createQuery(hql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
            }
            return query.setFirstResult(firstResult).setMaxResults(maxResult).list();
        } catch (Throwable e) {
            throw new DbException("执行[" + hql + "]出错.", e);
        }
    }

    /**
     * 
     * @param entryName
     * @param firstResult 记录开端.
     * @param maxResult 最大记录数.
     * @param queryMap 动态查询入参,参考:{@link BaseService.QueryHQLBuilder}.
     * @return 结果数组.
     * @throws DbException 查询出错时抛出.
     */
    @SuppressWarnings({ "rawtypes" })
    public List<Map> queryPage(String entryName, int firstResult, int maxResult, Map queryMap) throws DbException {
        String hql = "from " + entryName + " " + QueryHQLBuilder.build(queryMap);
        return this.queryHQLPage(hql, firstResult, maxResult);
    }

    /**
     * 自定义SQL查询.
     * 
     * @param sql sql语句.
     * @param values 替换入参.
     * @return 查询返回列表.
     * @throws DbException
     */
    @SuppressWarnings("unchecked")
    public List<Map<?, ?>> querySQL(String sql, Object... values) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            SQLQuery query = session.createSQLQuery(sql);
            if (values != null) {
                for (int i = 0; i < values.length; i++) {
                    query.setParameter(i, values[i]);
                }
            }
            return query.list();
        } catch (Throwable e) {
            throw new DbException("查询[" + sql + "]出错.", e);
        }
    }

    /**
     * 删除.
     * 
     * @param po 动态PO实例
     * @throws DbException 数据操作失败时抛出.
     */
    public void remove(Map<String, Object> po) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            session.delete(this.getEntryName(po), po);
        } catch (DbException e) {
            throw e;
        } catch (Throwable e) {
            throw new DbException("删除出错.", e);
        }
    }

    /**
     * 新增.
     * 
     * @param po 动态PO实例
     * @throws DbException 数据操作失败时抛出.
     */
    public void save(Map<String, Object> po) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            session.save(this.getEntryName(po), po);
        } catch (DbException e) {
            throw e;
        } catch (Throwable e) {
            throw new DbException("新增出错.", e);
        }
    }

    /**
     * 批量新增.
     * 
     * @param list 动态PO实例列表.
     * @throws DbException 数据操作失败时抛出.
     */
    public void saveBatch(List<Map<String, Object>> list) throws DbException {
        if (list == null || list.size() == 0) {
            return;
        }
        Session session = this.sessionFactory.getCurrentSession();
        try {
            for (Map<String, Object> po : list) {
                session.save(this.getEntryName(po), po);
            }
        } catch (DbException e) {
            throw e;
        } catch (Throwable e) {
            throw new DbException("新增出错.", e);
        }
    }

    /**
     * 新增或更新.
     * 
     * @param po 动态PO实例
     * @throws DbException 数据操作失败时抛出.
     */
    public void saveOrUpdate(Map<String, Object> po) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            session.saveOrUpdate(this.getEntryName(po), po);
        } catch (DbException e) {
            throw e;
        } catch (Throwable e) {
            throw new DbException("新增或更新出错.", e);
        }
    }

    /**
     * Spring容器使用.
     * 
     * @param sessionFactory
     */
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    /**
     * 更新.
     * 
     * @param po 动态PO实例
     * @throws DbException 数据操作失败时抛出.
     */
    public void update(Map<String, Object> po) throws DbException {
        Session session = this.sessionFactory.getCurrentSession();
        try {
            session.update(this.getEntryName(po), po);
        } catch (DbException e) {
            throw e;
        } catch (Throwable e) {
            throw new DbException("更新出错.", e);
        }
    }

    /**
     * 分页查询.
     * 
     * @param entryName 对象名.
     * @param start 起点.
     * @param limit 限制.
     * @return 对象集.
     * @throws DbException 数据操作失败时抛出.
     */
    public DataPackage queryPackage(String entryName, int start, int limit) throws DbException {
        DataPackage result = new DataPackage();
        result.setStart(start);
        result.setLimit(limit);
        result.setTotalRecord(getCount(entryName));
        result.setList(queryHQLPage("from " + entryName, result.getStart(), result.getLimit()));
        return result;
    }

    /**
     * 分页查询.
     * 
     * @param entryName 对象名.
     * @param start 起点.
     * @param limit 限制.
     * @param queryMap 动态查询入参,参考:{@link BaseService.QueryHQLBuilder}.
     * @return 对象集.
     * @throws DbException 数据操作失败时抛出.
     */
    @SuppressWarnings("rawtypes")
    public DataPackage queryPackage(String entryName, int start, int limit, Map queryMap) throws DbException {
        DataPackage result = new DataPackage();
        result.setStart(start);
        result.setLimit(limit);
        result.setTotalRecord(getCount(entryName, queryMap));
        result.setList(queryPage(entryName, result.getStart(), result.getLimit(), queryMap));
        return result;
    }

}
